package com.b511.swpu.cementbackend;

import com.b511.swpu.cementbackend.entity.BA_Well;
import com.b511.swpu.cementbackend.entity.Sys_Module;
import com.b511.swpu.cementbackend.entity.Sys_Module_Data;
import com.b511.swpu.cementbackend.repository.BA_WellRepository;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class InitBaWellData {

    @Autowired
    BA_WellRepository wellRepository;

    // 导入塘沽基地的井数据
    @Test
    public void importWellDataFromExcel(){
        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/Data.xlsx
            File file =  ResourceUtils.getFile("classpath:wellData.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("wellData");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            // add to List
            List<BA_Well> wellList = new ArrayList<>();

            // TODO: 清空表BA_WELL数据，读取并插入到表BA_WELL
            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    // 国别 0
                    // 区域 1
                    // 区块 2
                    // 构造/油田 3
                    // 井名 4
                    // 作业公司 7
                    BA_Well tmpWell = new BA_Well();
                    tmpWell.setCountry(row.getCell(0).toString());
                    tmpWell.setArea(row.getCell(1).toString());
                    tmpWell.setBlock(row.getCell(2).toString());
                    tmpWell.setOilField(row.getCell(3).toString());
                    tmpWell.setWellName(row.getCell(4).toString());
                    tmpWell.setCementCompany(row.getCell(7).toString());
                    wellList.add(tmpWell);
                }
            }
            if(0 < wellList.size()){
                wellRepository.saveAll(wellList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 导入深圳基地的井数据
    @Test
    public void importWellDataFormShenZhen(){
        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/Data.xlsx
            File file =  ResourceUtils.getFile("classpath:wellData.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("ShenZhen");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            // add to List
            List<BA_Well> wellList = new ArrayList<>();

            // TODO: 清空表BA_WELL数据，读取并插入到表BA_WELL
            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    // 国别 0
                    // 区域 1
                    // 区块 2
                    // 构造/油田 3
                    // 井名 4
                    // 作业公司 7
                    BA_Well tmpWell = new BA_Well();
                    tmpWell.setCountry(row.getCell(0).toString());
                    tmpWell.setArea(row.getCell(1).toString());
                    tmpWell.setBlock(row.getCell(2).toString());
                    tmpWell.setOilField(row.getCell(3).toString());
                    tmpWell.setWellName(row.getCell(4).toString());
                    tmpWell.setCementCompany(row.getCell(7).toString());
                    wellList.add(tmpWell);
                }
            }
            if(0 < wellList.size()){
                wellRepository.saveAll(wellList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // TODO: 2020.10.18 清空BA_WELL，重新导入数据
    @Test
    public void importWellData(){
        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/数据合集.xlsx
            File file =  ResourceUtils.getFile("classpath:数据合集.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("Sheet1");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            // add to List
            List<BA_Well> wellList = new ArrayList<>();

            // TODO: 清空表BA_WELL数据，读取并插入到表BA_WELL
            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    // 作业公司 1
                    // 井名 2
                    // 国别 3
                    // 区域 4
                    // 区块 5
                    // 油田 6
                    BA_Well tmpWell = new BA_Well();
                    tmpWell.setCementCompany(row.getCell(1).toString());
                    tmpWell.setWellName(row.getCell(2).toString());
                    tmpWell.setCountry(row.getCell(3).toString());
                    tmpWell.setArea(row.getCell(4).toString());
                    tmpWell.setBlock(row.getCell(5).toString());
                    tmpWell.setOilField(row.getCell(6).toString());
                    wellList.add(tmpWell);
                }
            }
            if(0 < wellList.size()){
                wellRepository.saveAll(wellList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
